package org.magicsoft.office.excel.impt;

import java.sql.Connection;
import java.util.Map;

import org.magicsoft.office.db.DatabaseUtils;
import org.magicsoft.office.excel.model.Column;
import org.magicsoft.office.excel.model.DefaultValue;
import org.magicsoft.office.excel.model.Sheet;
import org.magicsoft.office.excel.model.TargetTable;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class NewRecordsInserter {

	private static final Logger logger = LoggerFactory.getLogger(NewRecordsInserter.class);

	public void insert(Sheet sheet, Connection conn) {
		for (TargetTable targetTable : sheet.getTargetTables()) {
			if (targetTable.isJunctionTable()) {
				this.clearJunctionRecords(targetTable, sheet, conn);
			}
			this.insertNewRecords(targetTable, sheet, conn);
		}
	}

	private void clearJunctionRecords(TargetTable targetTable, Sheet sheet, Connection conn) {
		StringBuilder deleteBuilder = new StringBuilder();
		deleteBuilder.append("delete from ");
		deleteBuilder.append(targetTable.getTableName());
		deleteBuilder.append(" where ");
		deleteBuilder.append(sheet.getFirstColumn().getBindColumn());
		deleteBuilder.append(" in (");
		deleteBuilder.append("select ");
		deleteBuilder.append(sheet.getFirstColumn().getReference().getTempTableColumn());
		deleteBuilder.append(" from ");
		deleteBuilder.append(sheet.getTempTableName());
		deleteBuilder.append(")");
		String sql = deleteBuilder.toString();
		logger.debug("delete junction records sql:" + sql);
		DatabaseUtils.executeUpdate(conn, sql);
	}

	private void insertNewRecords(TargetTable targetTable, Sheet sheet, Connection conn) {
		StringBuilder insertBuilder = new StringBuilder();
		StringBuilder selectBuilder = new StringBuilder();

		insertBuilder.append("insert into ");
		insertBuilder.append(targetTable.getTableName());
		insertBuilder.append(" (");

		if (targetTable.getPrimaryKey() != null) {
			insertBuilder.append(targetTable.getPrimaryKey().getColumn());
			insertBuilder.append(", ");
			selectBuilder.append(targetTable.getNidColumn());
			selectBuilder.append(", ");
		}

		for (Map.Entry<String, Column> entry : sheet.getColumns().entrySet()) {
			if (entry.getValue().getReference() != null) {
				insertBuilder.append(entry.getValue().getBindColumn());
				insertBuilder.append(", ");

				selectBuilder.append(entry.getValue().getReference().getTempTableColumn());
				selectBuilder.append(", ");
			} else if (entry.getValue().getBindColumn() != null) {
				insertBuilder.append(entry.getValue().getBindColumn());
				insertBuilder.append(", ");

				selectBuilder.append(entry.getValue().getTempTableColumn());
				selectBuilder.append(", ");
			}
		}

		if (targetTable.getDefaultValues() != null) {
			for (DefaultValue defaultValue : targetTable.getDefaultValues()) {
				insertBuilder.append(defaultValue.getColumn());
				insertBuilder.append(", ");

				selectBuilder.append(sheet.getTempTableName());
				selectBuilder.append(".");
				selectBuilder.append(defaultValue.getColumn());
				selectBuilder.append(", ");
			}
		}

		insertBuilder.deleteCharAt(insertBuilder.length() - 2);
		selectBuilder.deleteCharAt(selectBuilder.length() - 2);

		insertBuilder.append(") select ");
		insertBuilder.append(selectBuilder);
		insertBuilder.append(" from ");
		insertBuilder.append(sheet.getTempTableName());
		insertBuilder.append(" where ");
		insertBuilder.append(sheet.getTempTableName());
		insertBuilder.append(".errormsg_ is null");
		if (targetTable.getPrimaryKey() != null) {
			insertBuilder.append(" and ");
			insertBuilder.append(sheet.getTempTableName());
			insertBuilder.append(".");
			insertBuilder.append(targetTable.getOidColumn());
			insertBuilder.append(" is null");
		}
		String sql = insertBuilder.toString();
		logger.debug("insert new records sql:" + sql);
		DatabaseUtils.executeUpdate(conn, sql);
	}

}
